package ls.impl;

import ls.dao.CourseDAO;
import ls.entity.Course;
import Connection.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CourseDAOImpl extends DAOBase implements CourseDAO {
    private static final String COURSE_INSERT_SQL =
            "INSERT INTO course VALUES(?,?,?,?,?,?,?,?,?) ";
    @Override
    public void addCourse(Course course) {
        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(COURSE_INSERT_SQL);
            ps.setString(1,course.getCourse_name());
            ps.setString(2,course.getCourse_teacher());
            ps.setString(3,course.getCourse_property());
            ps.setString(4,course.getCourse_student());
            ps.setString(5,course.getCourse_hour());
            ps.setString(6,course.getNumber());
            ps.setString(7,course.getCourse_state());
            ps.setString(8,course.getTeacher_id());
            ps.setString(9,course.getStudent_id());
            ps.executeUpdate();
            ps.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                connection.close();
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    private static final String Course_SELECT_SQL = "SELECT * FROM course where course_state=? order by course_hour desc";
    @Override
    public List<Course> getCourse() {
        List<Course> courseList = new ArrayList<>();
        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(Course_SELECT_SQL);
            ps.setString(1,"无助教");
            ResultSet rs = ps.executeQuery();
            while (rs.next())
            {
                String course_name = rs.getString("course_name");
                String course_teacher = rs.getString("course_teacher");
                String course_property = rs.getString("course_property");
                String course_student = rs.getString("course_student");
                String course_hour = rs.getString("course_hour");
                String number = rs.getString("number");
                String course_state = rs.getString("course_state");
                String teacher_id = rs.getString("teacher_id");
                String student_id = rs.getString("student_id");
                Course course1 = new Course(course_name,course_teacher,course_property,course_student,
                course_hour,number,course_state,teacher_id,student_id);
                courseList.add(course1);
            }
            rs.close();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return  courseList;
    }

    private static final String Course_SELECT_SQL2 = "SELECT * FROM course where course_name=?";
    @Override
    public Course look(String course_name) {
        Connection connection = null;
        Course course = new Course();
        try {
            connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(Course_SELECT_SQL2);
            ps.setString(1,course_name);
            ResultSet rs = ps.executeQuery();
            while (rs.next())
            {
                String course_teacher = rs.getString("course_teacher");
                String course_property = rs.getString("course_property");
                String course_student = rs.getString("course_student");
                String course_hour = rs.getString("course_hour");
                String number = rs.getString("number");
                String course_state = rs.getString("course_state");
                String teacher_id = rs.getString("teacher_id");
                String student_id = rs.getString("student_id");
                course.setCourse_name(course_name);
                course.setCourse_teacher(course_teacher);
                course.setCourse_property(course_property);
                course.setCourse_student(course_student);
                course.setCourse_hour(course_hour);
                course.setNumber(number);
                course.setCourse_state(course_state);
                course.setTeacher_id(teacher_id);
                course.setStudent_id(student_id);
            }
            rs.close();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return  course;
    }

    private static final String Course_SELECT_SQL3 = "SELECT course_name FROM course where course_state=?";
    @Override
    public List<String> getCourseName() {
        List<String> nameList = new ArrayList<>();
        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(Course_SELECT_SQL3);
            ps.setString(1,"无助教");
            ResultSet rs = ps.executeQuery();
            while (rs.next())
            {
                String course_name = rs.getString("course_name");
                nameList.add(course_name);
            }
            rs.close();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return  nameList;
    }

    private static final String Course_SELECT_SQL4 = "SELECT * FROM course where teacher_id=? and course_state=?";
    @Override
    public List<Course> chooseStu(String teacher_id) {
        List<Course> courseList = new ArrayList<>();
        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(Course_SELECT_SQL4);
            ps.setString(1,teacher_id);
            ps.setString(2,"无助教");
            ResultSet rs = ps.executeQuery();
            while (rs.next())
            {
                String course_name = rs.getString("course_name");
                String course_teacher = rs.getString("course_teacher");
                String course_property = rs.getString("course_property");
                String course_student = rs.getString("course_student");
                String course_hour = rs.getString("course_hour");
                String number = rs.getString("number");
                String course_state = rs.getString("course_state");
                String student_id = rs.getString("student_id");
                Course course1 = new Course(course_name,course_teacher,course_property,course_student,
                        course_hour,number,course_state,teacher_id,student_id);
                courseList.add(course1);
            }
            rs.close();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return  courseList;
    }

    private static final String COURSE_UPDATE_SQL =  "UPDATE course SET course_state=?,student_id=? WHERE course_name=?";
    @Override
    public void updateCourse(Course course) {

        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(COURSE_UPDATE_SQL);
            ps.setString(1,course.getCourse_state());
            ps.setString(2,course.getStudent_id());
            ps.setString(3,course.getCourse_name());
            ps.executeUpdate();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    private static final String Course_SELECT_SQL5 = "SELECT * FROM course where teacher_id=?";
    @Override
    public List<Course> getMyCourse(String teacher_id) {
        List<Course> courseList = new ArrayList<>();
        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(Course_SELECT_SQL5);
            ps.setString(1,teacher_id);
            ResultSet rs = ps.executeQuery();
            while (rs.next())
            {
                String course_name = rs.getString("course_name");
                String course_teacher = rs.getString("course_teacher");
                String course_property = rs.getString("course_property");
                String course_student = rs.getString("course_student");
                String course_hour = rs.getString("course_hour");
                String number = rs.getString("number");
                String course_state = rs.getString("course_state");
                String student_id = rs.getString("student_id");
                Course course1 = new Course(course_name,course_teacher,course_property,course_student,
                        course_hour,number,course_state,teacher_id,student_id);
                courseList.add(course1);
            }
            rs.close();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return  courseList;
    }

    private static final String Course_SELECT_SQL6 = "SELECT * FROM course";
    @Override
    public List<Course> getAllCourse() {
        List<Course> courseList = new ArrayList<>();
        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(Course_SELECT_SQL6);
            ResultSet rs = ps.executeQuery();
            while (rs.next())
            {
                String course_name = rs.getString("course_name");
                String course_teacher = rs.getString("course_teacher");
                String course_property = rs.getString("course_property");
                String course_student = rs.getString("course_student");
                String course_hour = rs.getString("course_hour");
                String number = rs.getString("number");
                String course_state = rs.getString("course_state");
                String teacher_id = rs.getString("teacher_id");
                String student_id = rs.getString("student_id");
                Course course1 = new Course(course_name,course_teacher,course_property,course_student,
                        course_hour,number,course_state,teacher_id,student_id);
                courseList.add(course1);
            }
            rs.close();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return  courseList;
    }

    private static final String COURSE_UPDATE_SQL2 =
            "UPDATE course SET course_property=?,course_student=?,course_hour=?,number=? WHERE course_name=?";
    @Override
    public void updateCourse2(Course course) {

        Connection connection = null;
        try {
            connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(COURSE_UPDATE_SQL2);
            ps.setString(1,course.getCourse_property());
            ps.setString(2,course.getCourse_student());
            ps.setString(3,course.getCourse_hour());
            ps.setString(4,course.getNumber());
            ps.setString(5,course.getCourse_name());
            ps.executeUpdate();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    private static final String COURSE_DELETE_SQL = "DELETE FROM course WHERE course_name=?";
    @Override
    public void deleteCourse(String course_name) {
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement ps = con.prepareStatement(COURSE_DELETE_SQL);
            ps.setString(1, course_name);
            ps.executeUpdate();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}
